package com.holly.unit.deform.util;

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import com.holly.unit.db.api.constants.DataBaseConstants;
import com.holly.unit.db.api.exception.DaoException;
import com.holly.unit.db.api.exception.enums.DatabaseExceptionEnum;
import com.holly.unit.db.api.pojo.druid.DruidProperties;
import com.holly.unit.db.api.util.DatabaseUtil;
import com.holly.unit.deform.api.pojo.online.pojo.ColumnInfo;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 类描述: 数据库操作加强类 继承db操作模块
 *
 * @author fxr
 * @version 1.0.0
 * @date 2022/1/14 19:27
 */
@Slf4j
public class DatabaseDeFormUtil {

    /**
     * 方法:读取表字段信息
     * @param tableName
     */
    public static List<ColumnInfo> findOriginalTableColumn(DruidProperties GenCodeCfg,String tableName){
        ArrayList<ColumnInfo> fieldList = new ArrayList<>();
        Connection conn = null;
        try {
            Class.forName(GenCodeCfg.getDriverClassName());
            conn = DriverManager.getConnection(
                    GenCodeCfg.getUrl(), GenCodeCfg.getUsername(), GenCodeCfg.getPassword());

            String sql =getCheckTableExitSql(GenCodeCfg.getUrl(),GenCodeCfg,tableName);
            PreparedStatement preparedStatement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();

            // 把指针指向结果集的最后一个
            resultSet.last();
            //取到最后一条结果的行号，作为属性的个数
            int columnNum = resultSet.getRow();
            int num = columnNum;

            //判断数据表中是否存在字段
            if (num > 0) {
                ColumnInfo columnInfo = new ColumnInfo();
                //代码中定义的属性字段
                columnInfo.setFieldName(resultSet.getString(1).toLowerCase());
                //数据库表字段名
                columnInfo.setColName(resultSet.getString(1).toUpperCase());
                //JAVA属性类型dbFieldNamedbFieldName
                columnInfo.setFieldType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                //数据库表字段类型
                columnInfo.setColType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                // 精度
                columnInfo.setPrecision(resultSet.getString(4));
                //小数点
                columnInfo.setScale(resultSet.getString(5));
                //字符串允许输入的最大长度
                columnInfo.setStrMaxLength(resultSet.getString(6));
                //是否允许为空0否 1是
                columnInfo.setNullAble(getNullAble(resultSet.getString(7)));
                //字段注释
                columnInfo.setColComment(StrUtil.isBlank(resultSet.getString(3))?columnInfo.getFieldName():resultSet.getString(3));
                //表字段默认值
                columnInfo.setDbDefaultVal(StrUtil.isNotEmpty(resultSet.getString(8))?resultSet.getString(8):"");
                columnInfo.setDbIsKey(StrUtil.isNotEmpty(resultSet.getString(9))?1:0);

                fieldList.add(columnInfo);
                while (resultSet.previous()) {
                    ColumnInfo po = new ColumnInfo();


                    po.setFieldName(resultSet.getString(1).toLowerCase());
                    //保存字段名
                    po.setColName(resultSet.getString(1).toUpperCase());
                    po.setFieldType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                    po.setColType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                    log.debug("-----po.setFieldType------------"+po.getFieldType());
                    //精度-小数点
                    po.setPrecision(resultSet.getString(4));
                    po.setScale(resultSet.getString(5));
                    po.setStrMaxLength(resultSet.getString(6));
                    po.setNullAble(getNullAble(resultSet.getString(7)));
                    po.setColComment(StrUtil.isBlank(resultSet.getString(3))?po.getFieldName():resultSet.getString(3));
                    po.setDbDefaultVal(StrUtil.isNotEmpty(resultSet.getString(8))?resultSet.getString(8):"");
                    po.setDbIsKey(StrUtil.isNotEmpty(resultSet.getString(9))?1:0);
                    fieldList.add(po);
                }
            }else {
                throw new Exception("该表不存在或者表中没有字段");
            }

            log.debug("读取表成功");
            return fieldList;
        } catch (Exception ex) {
            log.error("查询表的所有字段错误！", ex);
            throw new DaoException(DatabaseExceptionEnum.FIELD_GET_ERROR, ex.getMessage());
        } finally {
            IoUtil.close(conn);
        }
    }

    /***
     * 根据数据库类型和表名 获取判断表是否存在的SQL
     * @param url 配置url连接
     * @param tableName 表名
     * @return
     */
    private static String getCheckTableExitSql(String url, DruidProperties druidProperties, String tableName) throws Exception {
        // 获取数据库名称
        String dbName = DatabaseUtil.getDbName(druidProperties);
        String dbType = DatabaseUtil.getDatabaseTypeByDataSource();
        String sql = "";
        //mysql
        if(dbType.equals(DataBaseConstants.DB_TYPE_MYSQL)){
            sql = "select column_name,column_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable,column_default,column_key " +
                    "from information_schema.columns where table_name = "+DbTypeUtil.judgeIsNumber(tableName)+" "+
                    "and table_schema = "+DbTypeUtil.judgeIsNumber(dbName); // 表名
        }
        //oracle
        if(dbType.equals(DataBaseConstants.DB_TYPE_ORACLE)){
            sql = "select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment"
                    + " from user_tab_cols colstable "
                    + " inner join user_col_comments commentstable "
                    + " on colstable.column_name = commentstable.column_name "
                    + " where colstable.table_name = commentstable.table_name "
                    + " and colstable.table_name = "+DbTypeUtil.judgeIsNumber(tableName.toUpperCase());
        }
        //postgresql
        if(dbType.equals(DataBaseConstants.DB_TYPE_POSTGRESQL)){
            sql = MessageFormat.format(url,DbTypeUtil.judgeIsNumber(tableName.toUpperCase()),DbTypeUtil.judgeIsNumber(tableName.toUpperCase()));
        }
        //sqlserver
        if(dbType.equals(DataBaseConstants.DB_TYPE_SQLSERVER)){
            sql = MessageFormat.format(url,DbTypeUtil.judgeIsNumber(tableName));
        }

        return sql;
    }

    /***
     * 返回字段是否为空属性
     * @param nullAble
     * @return
     */
    public static Integer getNullAble(String nullAble) {
        if ("YES".equals(nullAble) || "yes".equals(nullAble) ||
                "y".equals(nullAble) || "Y".equals(nullAble) || "f".equals(nullAble)) {
            return 0;
        }

        if ("NO".equals(nullAble) || "no".equals(nullAble) ||
                "N".equals(nullAble) || "n".equals(nullAble) || "t".equals(nullAble)) {
            return 1;
        }
        return null;
    }

}
